# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import openpyxl as openpyxl
import pymysql


class XiechengPipeline:

    def __init__(self):
        self.contact = pymysql.connect(
            host='8.137.150.77',
            port=3303,
            user='root',
            password='123456',
            database='scrapy',
            charset='utf8mb4'
        )
        self.cursor = self.contact.cursor()
        self.data = []
        self.item = None
        self.number = 0

    def open_spider(self, spider):
        print('MysqlPipeline:', '开始抓取。。。。！')

    def close_spider(self, spider):
        if len(self.data) > 0:
            self._write_mysql_()
        self.contact.close()
        print(f'数据存入Mysql, 总计{self.number}次')

    def process_item(self, item, spider):
        name = item.get('name', '')
        type = item.get('type', '')
        time = item.get('time', '')
        page = item.get('page', '')
        url = item.get('url', '')
        self.data.append((name, type, time, page, url))
        if len(self.data) == 100:
            self.number += 1
            self._write_mysql_()
            self.data.clear()
        return item

    def _write_mysql_(self):
        for self.item in self.data:
            print('data:', self.item)
            self.cursor.execute(
                'insert into game (name, type, time, page, url) values (%s, %s, %s, %s, %s)',
                self.item
            )
            self.contact.commit()



class ExcelPipeline:
    # 创建excel表
    def __init__(self):
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = '4399_Game'
        self.ws.append(('name', 'type', 'time', 'page', 'url'))

    def open_spider(self, spider):
        print('ExcelPipeline:', '开始抓取。。。。！')

    def close_spider(self, spider):
        self.wb.save('4399_Game.xlsx')
        print('数据存入Excel')

    # 拿到数据，提取对应的值，存入表中
    def process_item(self, item, spider):
        name = item.get('name', '')
        type = item.get('type', '')
        time = item.get('time', '')
        page = item.get('page', '')
        url = item.get('url', '')
        self.ws.append((name, type, time, page, url))
        return item
